package nnsom;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;

public class DataGenerator {

	Connection connect = null;
	PreparedStatement pstmtFeedRecords = null, pstmtLiveData = null;
	ResultSet resultSet = null;

	String subscriber_id;
	Calendar begin_time;
	Calendar end_time;

	public DataGenerator(Connection connect, String subscriber_id,
			Calendar begin_time, Calendar end_time) {
		this.connect = connect;
		this.subscriber_id = subscriber_id;
		this.begin_time = begin_time;
		this.end_time = end_time;

		if (connect != null) {
			try {
				pstmtFeedRecords = connect
						.prepareStatement("select  COUNT(*) as nbr_calls, AVG(duration) as avg_duration , SUM(is_international) as is_international, "
								+ "SUM(is_premium) as is_premium , SUM(is_free) as is_free, sum(is_fixed) as is_fixed, sum(is_hidden) as is_hidden, "
								+ "sum(on_weekend) as on_weekend from calls where src_id = ? and FROM_UNIXTIME(timestamp) >= ? and FROM_UNIXTIME(timestamp) <= ?");
				pstmtLiveData = connect
						.prepareStatement("select  COUNT(*) as nbr_calls, AVG(duration) as avg_duration , SUM(is_international) as is_international, "
								+ "SUM(is_premium) as is_premium , SUM(is_free) as is_free, sum(is_fixed) as is_fixed, sum(is_hidden) as is_hidden, "
								+ "sum(on_weekend) as on_weekend from calls where src_id = ? and FROM_UNIXTIME(timestamp) >= ? and FROM_UNIXTIME(timestamp) <= ?");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// not used
	public List<FeedRecord> generateFeedRecords(int upper_limit) {
		List<FeedRecord> frs = generateFeedRecordsStoredProcedure(60);
		if (upper_limit <= frs.size())
			return frs;
		else {
			Collections.shuffle(frs);
			int records_to_be_removed = frs.size() - upper_limit;
			for (int i = 0; i < records_to_be_removed; i++) {
				frs.remove(0);
			}
			return frs;
		}
	}

	public List<FeedRecord> generateFeedRecordsSqlQueries() {
		List<FeedRecord> frs = new ArrayList<FeedRecord>();
		try {
			Calendar time = Calendar.getInstance();
			time.set(Calendar.YEAR, begin_time.get(Calendar.YEAR));
			time.set(Calendar.MONTH, begin_time.get(Calendar.MONTH));
			time.set(Calendar.DATE, begin_time.get(Calendar.DATE));
			time.set(Calendar.HOUR_OF_DAY, begin_time.get(Calendar.HOUR_OF_DAY));
			time.set(Calendar.MINUTE, begin_time.get(Calendar.MINUTE));
			time.set(Calendar.SECOND, begin_time.get(Calendar.SECOND));
			do {
				FeedRecord fr = new FeedRecord();
				pstmtFeedRecords.setString(1, subscriber_id);
				java.sql.Timestamp start = new java.sql.Timestamp(time
						.getTime().getTime());
				pstmtFeedRecords.setTimestamp(2, start);
				fr.from_time = String.format("%1$te-%1$tm-%1$ty %1$tT", time);
				fr.quarter = getQuarter(time);
				time.add(Calendar.HOUR, 1);
				java.sql.Timestamp end = new java.sql.Timestamp(time.getTime()
						.getTime());
				fr.to_time = String.format("%1$te-%1$tm-%1$ty %1$tT", time);
				pstmtFeedRecords.setTimestamp(3, end);
				resultSet = pstmtFeedRecords.executeQuery();
				resultSet.next();
				fr.nbr_calls = resultSet.getInt("nbr_calls");
				fr.avg_call_duration = resultSet.getDouble("avg_duration");
				fr.perc_free = resultSet.getInt("is_free")
						/ (double) fr.nbr_calls;
				fr.perc_international = resultSet.getInt("is_international")
						/ (double) fr.nbr_calls;
				fr.perc_premium = resultSet.getInt("is_premium")
						/ (double) fr.nbr_calls;
				fr.perc_fixed = resultSet.getInt("is_fixed")
						/ (double) fr.nbr_calls;
				fr.perc_hidden = resultSet.getInt("is_hidden")
						/ (double) fr.nbr_calls;
				fr.on_weekend = resultSet.getInt("on_weekend") > 0 ? 1 : -1;
				// System.out.println(debug);
				if (fr.nbr_calls > 0)
					frs.add(fr);
			} while (time.before(end_time));
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return frs;
	}

	public List<FeedRecord> generateFeedRecordsStoredProcedure(int minutes) {
		List<FeedRecord> frs = new ArrayList<FeedRecord>();
		try {
			Calendar time = Calendar.getInstance();
			String testProc = "{ call batch_selects(?,?,?,?) }";
			CallableStatement cs = connect.prepareCall(testProc);
			long begin_time_L = begin_time.getTime().getTime() / 1000;
			long end_time_L = end_time.getTime().getTime() / 1000;
			cs.setString(1, subscriber_id);
			cs.setLong(2, begin_time_L);
			cs.setLong(3, end_time_L);
			cs.setLong(4, minutes);
			cs.executeQuery();
			ResultSet rs = cs.getResultSet();
			boolean b = false;
			do {
				boolean exist = rs.next();
				if (exist) {
					if (rs.getInt("nbr_calls") > 0) {
						FeedRecord fr = new FeedRecord();
						long from_time_L = rs.getLong("from_time") * 1000;
						long to_time_L = rs.getLong("to_time") * 1000;
						time.setTimeInMillis(from_time_L);
						fr.from_time = String.format("%1$te-%1$tm-%1$ty %1$tT",
								time);
						fr.quarter = getQuarter(time);
						time.setTimeInMillis(to_time_L);
						fr.to_time = String.format("%1$te-%1$tm-%1$ty %1$tT",
								time);
						fr.nbr_calls = rs.getInt("nbr_calls");
						fr.avg_call_duration = rs.getDouble("avg_duration");
						fr.perc_free = rs.getInt("is_free")
								/ (double) fr.nbr_calls;
						fr.perc_international = rs.getInt("is_international")
								/ (double) fr.nbr_calls;
						fr.perc_premium = rs.getInt("is_premium")
								/ (double) fr.nbr_calls;
						fr.perc_fixed = rs.getInt("is_fixed")
								/ (double) fr.nbr_calls;
						fr.perc_hidden = rs.getInt("is_hidden")
								/ (double) fr.nbr_calls;
						fr.on_weekend = rs.getInt("on_weekend") > 0 ? 1 : -1;
						frs.add(fr);
					}
				}
				b = cs.getMoreResults();
				if (b)
					rs = cs.getResultSet();
			} while (b);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return frs;
	}

	private int getQuarter(Calendar from_time) {
		int hour_of_day = from_time.get(Calendar.HOUR_OF_DAY);
		if ((hour_of_day >= 1) && (hour_of_day <= 6))
			return 0;
		else if ((hour_of_day >= 7) && (hour_of_day <= 12))
			return 1;
		else if ((hour_of_day >= 13) && (hour_of_day <= 18))
			return 2;
		else
			return 3;
	}

	public FeedRecord liveData() {
		FeedRecord fr = new FeedRecord();
		try {
			Calendar time = Calendar.getInstance();
			time.set(Calendar.YEAR, end_time.get(Calendar.YEAR));
			time.set(Calendar.MONTH, end_time.get(Calendar.MONTH));
			time.set(Calendar.DATE, end_time.get(Calendar.DATE));
			time.set(Calendar.HOUR_OF_DAY, end_time.get(Calendar.HOUR_OF_DAY));
			time.set(Calendar.MINUTE, end_time.get(Calendar.MINUTE));
			time.set(Calendar.SECOND, end_time.get(Calendar.SECOND));

			pstmtLiveData.setString(1, subscriber_id);
			java.sql.Timestamp start = new java.sql.Timestamp(time.getTime()
					.getTime());
			pstmtLiveData.setTimestamp(2, start);
			fr.from_time = String.format("%1$te-%1$tm-%1$ty %1$tT", time);
			fr.quarter = getQuarter(time);
			time.add(Calendar.HOUR, 1);
			java.sql.Timestamp end = new java.sql.Timestamp(time.getTime()
					.getTime());
			fr.to_time = String.format("%1$te-%1$tm-%1$ty %1$tT", time);
			pstmtLiveData.setTimestamp(3, end);
			resultSet = pstmtLiveData.executeQuery();
			resultSet.next();
			fr.nbr_calls = resultSet.getInt("nbr_calls");
			fr.avg_call_duration = resultSet.getDouble("avg_duration");
			fr.perc_free = resultSet.getInt("is_free") / (double) fr.nbr_calls;
			fr.perc_international = resultSet.getInt("is_international")
					/ (double) fr.nbr_calls;
			fr.perc_premium = resultSet.getInt("is_premium")
					/ (double) fr.nbr_calls;
			fr.perc_fixed = resultSet.getInt("is_fixed")
					/ (double) fr.nbr_calls;
			fr.perc_hidden = resultSet.getInt("is_hidden")
					/ (double) fr.nbr_calls;
			fr.on_weekend = resultSet.getInt("on_weekend") > 0 ? 1 : -1;
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		if (fr.nbr_calls == 0)
			return null;
		else
			return fr;
	}

	public void oneHourForward() {
		end_time.add(Calendar.HOUR, 1);
	}

}
